package mb.admin.dao;

import java.sql.Date;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.faces.model.SelectItem;
import javax.xml.xpath.XPathExpressionException;

import mb.admin.data.CustomersData;
import mb.admin.data.ParcelData;
import mb.commons.AbstractDAO;
import mb.commons.DbConnection;
import mb.commons.XmlHelper;

/**
 * Klasa dostępu do bazy danych dla managera paczek
 * 
 * @author Michał Budziło
 * 
 */
public class ParcelDAO extends AbstractDAO {

	/**
	 * Konstruktor
	 */
	public ParcelDAO() {
		conn = DbConnection.getConnection();
		helper = new XmlHelper("WEB-INF/xml/admin/parcels.xml");
	}

	public ArrayList<ParcelData> getParcelList() {
		ArrayList<ParcelData> list = new ArrayList<ParcelData>();
		ParcelData data;
		try {
			query = helper.getSqlString("parcelList",
					"sqlString/prepareMain/sql");
			stmt = conn.createStatement();
			rs = stmt.executeQuery(query);
			while (rs.next()) {
				data = new ParcelData();

				data.setIdentPaczki(rs.getInt("id_paczki"));
				data.setIdentNadawcy(rs.getInt("id_nadawcy"));
				data.setImieNadawcy(rs.getString("imie1"));
				data.setNazwiskoNadawcy(rs.getString("nazwisko1"));
				data.setAdresNadawcy(rs.getString("adres1"));
				data.setKodNadawcy(rs.getString("kod1"));
				data.setMiastoNadawcy(rs.getString("miejscowosc1"));
				data.setImieOdbiorcy(rs.getString("imie2"));
				data.setNazwiskoOdbiorcy(rs.getString("nazwisko2"));
				data.setAdresOdbiorcy(rs.getString("adres2"));
				data.setKodOdbiorcy(rs.getString("kod2"));
				data.setMiastoOdbiorcy(rs.getString("miejscowosc2"));
				data.setDataNadania(rs.getDate("data_nad"));
				data.setDataOdbioru(rs.getDate("data_dor"));
				data.setOplata(rs.getDouble("oplata"));
				data.setPobranie(rs.getDouble("pobranie"));
				
				//data.setUwagi(rs.getString("uwagi"));

				list.add(data);
			}
			return list;
		} catch (SQLException e) {
			error(e.getErrorCode() + " : " + e.getMessage());
			e.printStackTrace();
		} catch (XPathExpressionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;
	}

	/**
	 * Metoda wstawia nową paczkę do bazy danych
	 * 
	 * @param data
	 *            obiekt paczki
	 */
	public void insertData(ParcelData data) {
		try {
			query = helper.getSqlString("insertData",
					"sqlString/prepareMain/prepareStatment");
			prepStmt = conn.prepareStatement(query);
			if (data.getIdentNadawcy() == null || data.getIdentNadawcy()==0)
				prepStmt.setNull(1, java.sql.Types.INTEGER);
			else
				prepStmt.setInt(1, data.getIdentNadawcy());

			prepStmt.setString(2, data.getImieNadawcy());
			prepStmt.setString(3, data.getNazwiskoNadawcy());
			prepStmt.setString(4, data.getAdresNadawcy());
			prepStmt.setString(5, data.getKodNadawcy());
			prepStmt.setString(6, data.getMiastoNadawcy());
			prepStmt.setString(7, data.getImieOdbiorcy());
			prepStmt.setString(8, data.getNazwiskoOdbiorcy());
			prepStmt.setString(9, data.getAdresOdbiorcy());
			prepStmt.setString(10, data.getKodOdbiorcy());
			prepStmt.setString(11, data.getMiastoOdbiorcy());
			prepStmt.setDate(12, (Date) data.getDataNadania());
			prepStmt.setDate(13, (Date) data.getDataOdbioru());
			prepStmt.setDouble(14, data.getOplata());
			prepStmt.setDouble(15, data.getPobranie());
			//prepStmt.setString(14, data.getUwagi());

			prepStmt.executeUpdate();

		} catch (SQLException e) {
			error(e.getErrorCode() + " : " + e.getMessage());
			e.printStackTrace();
		} catch (XPathExpressionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {

			close();
		}

	}

	/**
	 * Metoda aktualizuje wybraną paczkę w bazie danych
	 * 
	 * @param data
	 *            obiekt paczki
	 */
	public void updateData(ParcelData data) {
		try {
			query = helper.getSqlString("updateData",
					"sqlString/prepareMain/prepareStatment");
			prepStmt = conn.prepareStatement(query);

			prepStmt.setString(1, data.getImieNadawcy());
			prepStmt.setString(2, data.getNazwiskoNadawcy());
			prepStmt.setString(3, data.getAdresNadawcy());
			prepStmt.setString(4, data.getKodNadawcy());
			prepStmt.setString(5, data.getMiastoNadawcy());
			prepStmt.setString(6, data.getImieOdbiorcy());
			prepStmt.setString(7, data.getNazwiskoOdbiorcy());
			prepStmt.setString(8, data.getAdresOdbiorcy());
			prepStmt.setString(9, data.getKodOdbiorcy());
			prepStmt.setString(10, data.getMiastoOdbiorcy());
			prepStmt.setDate(11, data.getDataNadania());
			prepStmt.setDate(12, data.getDataOdbioru());
			prepStmt.setDouble(13, data.getOplata());
			prepStmt.setDouble(14, data.getPobranie());
			//prepStmt.setString(13, data.getUwagi());
			prepStmt.setInt(15, data.getIdentPaczki());

			prepStmt.executeUpdate();

		} catch (SQLException e) {
			error(e.getErrorCode() + " : " + e.getMessage());
			e.printStackTrace();
		} catch (XPathExpressionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {

			close();
		}

	}

	/**
	 * metoda usuwa z bazy danych wybraną paczkę
	 * 
	 * @param data
	 *            obiekt paczki
	 */
	public void deleteData(ParcelData data) {

		try {
			query = helper.getSqlString("deleteData",
					"sqlString/prepareMain/prepareStatment");
			prepStmt = conn.prepareStatement(query);
			prepStmt.setInt(1, data.getIdentPaczki());

			prepStmt.executeUpdate();

		} catch (SQLException e) {
			if(e.getErrorCode()==1451)
			{
				error("Nie można usunąć paczki, jest dodana do zamówienia");
			}else
			{
				error(e.getErrorCode() + " : " + e.getMessage());
			}
			
			e.printStackTrace();
		} catch (XPathExpressionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {

			close();
		}
	}

	/**
	 * Metoda pobiera z bazy danych listę id klientów
	 * 
	 * @return lista id
	 */
	public ArrayList<SelectItem> getIdNadawcyList() {

		ArrayList<SelectItem> list = new ArrayList<SelectItem>();
		try {
			query = helper.getSqlString("getIdNadawcyList",
					"sqlString/prepareMain/sql");
			stmt = conn.createStatement();
			rs = stmt.executeQuery(query);
			SelectItem tmp1 = new SelectItem(null, "nowy klient");
			list.add(tmp1);
			while (rs.next()) {
				SelectItem tmp = new SelectItem();
				tmp.setLabel(rs.getString("nazwisko") + " "
						+ rs.getString("imie") + " (" + rs.getInt("id_klienta")
						+ " ) ");
				tmp.setValue(rs.getInt("id_klienta"));

				list.add(tmp);
			}
			return list;
		} catch (SQLException e) {
			error(e.getErrorCode() + " : " + e.getMessage());
			e.printStackTrace();
		} catch (XPathExpressionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;
	}

	/**
	 * Metoda pobiera dane klineta z bazy danych na podstawie jego ID
	 * 
	 * @param identNadawcy
	 *            id klienta
	 * @return obiekt klienta
	 */
	public CustomersData getCustomerById(Integer identNadawcy) {
		CustomersData data = new CustomersData();
		try {
			query = helper.getSqlString("getCustomerById",
					"sqlString/prepareMain/prepareStatment");
			prepStmt = conn.prepareStatement(query);
			prepStmt.setInt(1, identNadawcy);
			rs = prepStmt.executeQuery();

			if (rs.next()) {
				data.setImie(rs.getString("imie"));
				data.setNazwisko(rs.getString("nazwisko"));
				data.setAdres(rs.getString("adres"));
				data.setMiasto(rs.getString("miasto"));
				data.setKod(rs.getString("kod"));

			}
			return data;

		} catch (SQLException e) {
			error(e.getErrorCode() + " : " + e.getMessage());
			e.printStackTrace();
		} catch (XPathExpressionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;

	}

}
